﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[esf_files].[P_ListFileReferenceLevel]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [esf_files].P_ListFileReferenceLevel
GO


-- esf_files.P_ListFileReferenceLevel 3008, 1
-- esf_files.P_ListFileReferenceLevel 3008, 2
-- esf_files.P_ListFileReferenceLevel 3008, 1, NULL, '<FRLs><FRL Level="2" Name="20" /></FRLs>'
-- esf_files.P_ListFileReferenceLevel 3008, 2, NULL, '<FRLs><FRL Level="1" Name="CALI" /></FRLs>'

CREATE PROCEDURE esf_files.P_ListFileReferenceLevel
	@pMonitoredDirectoryID INT
	,@pLevel INT
	,@pName VARCHAR (1000) = NULL
	,@pXMLMultiLevelsSearch XML = NULL
AS

	SELECT 
		DISTINCT 
		FRL.Level, 
		FRL.Name
	FROM
		esf_files.FileReferenceLevel FRL
	INNER JOIN
		esf_files.FileReference FR ON FR.ID = FRL.FileReferenceID
	WHERE 
		FR.MonitoredDirectoryID = @pMonitoredDirectoryID
		AND (@pName IS NULL OR FRL.Name LIKE @pName)
		AND Level = @pLevel
		AND (@pXMLMultiLevelsSearch IS NULL OR EXISTS 
		(
			SELECT 
				FRLX.FileReferenceID
			FROM
				esf_files.FileReferenceLevel FRLX
			INNER JOIN
				(SELECT
					MLS.F.value ('@Level', 'int') AS Level
					,MLS.F.value ('@Name', 'varchar (MAX)') AS Name
				FROM
					@pXMLMultiLevelsSearch.nodes ('/FRLs/child::node ()') MLS (F)) MLS 
			ON 
				MLS.Level = FRLX.Level
				AND MLS.Name = FRLX.Name
			WHERE 
				FRLX.FileReferenceID = FRL.FileReferenceID
		))
	ORDER BY
		FRL.Name
		
	
	